<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Connection state</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.usage.html">Running statements</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.sqlhints.html">SQL Hints</a></div>
 <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.quickstart.connectionpooling" class="section">
  <h2 class="title">Connection state</h2>
  <p class="para">
   The plugin changes the semantics of a PHP MySQL connection handle.
   A new connection handle represents a connection pool, instead of a
   single MySQL client-server network connection. The connection pool consists
   of a master connection, and optionally any number of slave connections.
  </p>
  <p class="para">
   Every connection from the connection pool has its own state. For example,
   SQL user variables, temporary tables and transactions are part of the state.
   For a complete list of items that belong to the state of a connection, see the
   <a href="mysqlnd-ms.pooling.html" class="link">connection pooling and switching</a>
   concepts documentation.
   If the plugin decides to switch connections for load balancing, the
   application could be given a connection which has a different state.
   Applications must be made aware of this.
  </p>
  <p class="para">
   <div class="example" id="example-1731">
    <p><strong>Example #1 Plugin config with one slave and one master</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">{
    &quot;myapp&quot;: {
        &quot;master&quot;: {
            &quot;master_0&quot;: {
                &quot;host&quot;: &quot;localhost&quot;,
                &quot;socket&quot;: &quot;\/tmp\/mysql.sock&quot;
            }
        },
        &quot;slave&quot;: {
            &quot;slave_0&quot;: {
                &quot;host&quot;: &quot;192.168.2.27&quot;,
                &quot;port&quot;: &quot;3306&quot;
            }
        }
    }
}</pre>
</div>
    </div>

   </div>
  </p>
  <p class="para">
   <div class="example" id="example-1732">
    <p><strong>Example #2 Pitfall: connection state and SQL user variables</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"username"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br />&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;Of&nbsp;course,&nbsp;your&nbsp;error&nbsp;handling&nbsp;is&nbsp;nicer...&nbsp;*/<br />&nbsp;&nbsp;</span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(),&nbsp;</span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/*&nbsp;Connection&nbsp;1,&nbsp;connection&nbsp;bound&nbsp;SQL&nbsp;user&nbsp;variable,&nbsp;no&nbsp;SELECT&nbsp;thus&nbsp;run&nbsp;on&nbsp;master&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SET&nbsp;@myrole='master'"</span><span style="color: #007700">))&nbsp;{<br />&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">/*&nbsp;Connection&nbsp;2,&nbsp;run&nbsp;on&nbsp;slave&nbsp;because&nbsp;SELECT&nbsp;*/<br /></span><span style="color: #007700">if&nbsp;(!(</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;@myrole&nbsp;AS&nbsp;_role"</span><span style="color: #007700">)))&nbsp;{<br />&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d]&nbsp;%s\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}&nbsp;else&nbsp;{<br />&nbsp;</span><span style="color: #0000BB">$row&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">();<br />&nbsp;</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">close</span><span style="color: #007700">();<br />&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"@myrole&nbsp;=&nbsp;'%s'\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_role'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

<div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
@myrole = &#039;&#039;
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   The example opens a load balanced connection and executes two statements.
   The first statement <em>SET @myrole=&#039;master&#039;</em> does not begin
   with the string <em>SELECT</em>. Therefore the plugin does not
   recognize it as a read-only query which shall be run on a slave. The
   plugin runs the statement on the connection to the master. The statement
   sets a SQL user variable which is bound to the master connection. The
   state of the master connection has been changed.
  </p>
  <p class="para">
   The next statement is <em>SELECT @myrole AS _role</em>.
   The plugin does recognize it as a read-only query and sends it to
   the slave. The statement is run on a connection to the slave. This
   second connection does not have any SQL user variables bound to it.
   It has a different state than the first connection to the master.
   The requested SQL user variable is not set. The example script prints
   <em>@myrole = &#039;&#039;</em>.
  </p>
  <p class="para">
   It is the responsibility of the application developer to take care
   of the connection state. The plugin does not monitor all
   connection state changing activities. Monitoring all possible cases would
   be a very CPU intensive task, if it could be done at all.
  </p>
  <p class="para">
   The pitfalls can easily be worked around using SQL hints.
  </p>
  </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.usage.html">Running statements</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.sqlhints.html">SQL Hints</a></div>
 <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
